package com.leave.dao;

import com.leave.entity.Classes;
import com.leave.entity.Leave;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public class LeaveDao {
    private JdbcTemplate jdbcTemplate;
   public List<Leave> query(Leave leave){
       String sql ="select * from sys_leave where 1=1 ";
       List<Object> paramValues=new ArrayList<>();
       List<Leave> leaveList=new ArrayList<>();
       if(leave!=null) {
           if (leave.getCourseID() != null && !"".equals(StringUtils.trim(leave.getCourseID()))) {
               sql += " and courseID=?";
               paramValues.add(leave.getCourseID());
           }
       }
//       System.out.println(sql);
       leaveList =jdbcTemplate.query(sql, paramValues.toArray(), new RowMapper<Leave>() {
           @Override
           public Leave mapRow(ResultSet rs, int rowNum) throws SQLException {
               Leave leave1=new Leave();
               String leaveID =rs.getString("leaveID");
               String courseID =rs.getString("courseID");
               String reason =rs.getString("reason");
               String dayNum =rs.getString("dayNum");
               String stuNo =rs.getString("stuNo");
               Timestamp applyTime =rs.getTimestamp("applyTime");
               String status =rs.getString("status");
               Timestamp auditTime =rs.getTimestamp("auditTime");
               String opinion =rs.getString("opinion");
               leave1.setLeaveID(leaveID);
               leave1.setCourseID(courseID);
               leave1.setApplyTime(applyTime);
               leave1.setOpinion(opinion);
               leave1.setReason(reason);
               leave1.setStatus(status);
               leave1.setAuditTime(auditTime);
               leave1.setDayNum(dayNum);
               leave1.setStuNo(stuNo);
               return leave1;
           }
       });
       return leaveList;
   }

    /**
     * 按班级查询
     * @param
     */
    public List<Leave> queryByClassID(String classID) {
        String sql = "SELECT l.leaveID, l.courseID, l.reason, l.dayNum, l.stuNo, l.applyTime, l.status, l.auditTime, l.opinion, c.classID " +
                "FROM sys_leave l " +
                "LEFT JOIN sys_course c ON l.courseID = c.courseID " +
                "WHERE c.classID = ?";
        List<Leave> leaveList = jdbcTemplate.query(sql, new Object[]{classID}, new RowMapper<Leave>() {
            @Override
            public Leave mapRow(ResultSet rs, int rowNum) throws SQLException {
                Leave leave = new Leave();
                leave.setLeaveID(rs.getString("leaveID"));
                leave.setCourseID(rs.getString("courseID"));
                leave.setReason(rs.getString("reason"));
                leave.setDayNum(rs.getString("dayNum"));
                leave.setStuNo(rs.getString("stuNo"));
                leave.setApplyTime(rs.getTimestamp("applyTime"));
                leave.setStatus(rs.getString("status"));
                leave.setAuditTime(rs.getTimestamp("auditTime"));
                leave.setOpinion(rs.getString("opinion"));
                // 这里不需要设置classID，因为它是通过课程表连接得到的
                return leave;
            }
        });

        return leaveList;
    }


    /**
     * 按学期查询
     * @param term
     * @return
     */
    public List<Leave> queryByTerm(String term) {
        String sql = "SELECT l.leaveID, l.courseID, l.reason, l.dayNum, l.stuNo, l.applyTime, l.status, l.auditTime, l.opinion, c.classID " +
                "FROM sys_leave l " +
                "LEFT JOIN sys_course c ON l.courseID = c.courseID " +
                "WHERE c.term = ?";
        List<Leave> leaveList = jdbcTemplate.query(sql, new Object[]{term}, new RowMapper<Leave>() {
            @Override
            public Leave mapRow(ResultSet rs, int rowNum) throws SQLException {
                Leave leave = new Leave();
                leave.setLeaveID(rs.getString("leaveID"));
                leave.setCourseID(rs.getString("courseID"));
                leave.setReason(rs.getString("reason"));
                leave.setDayNum(rs.getString("dayNum"));
                leave.setStuNo(rs.getString("stuNo"));
                leave.setApplyTime(rs.getTimestamp("applyTime"));
                leave.setStatus(rs.getString("status"));
                leave.setAuditTime(rs.getTimestamp("auditTime"));
                leave.setOpinion(rs.getString("opinion"));
                // 这里不需要设置classID，因为它是通过课程表连接得到的
                return leave;
            }
        });

        return leaveList;
    }

    /**
     * 按班级和学期查询
     * @param classID
     * @param term
     * @return
     */
    public List<Leave> seekByClassIDAndTerm(String classID, String term) {
        String sql = "SELECT l.leaveID, l.courseID, l.reason, l.dayNum, l.stuNo, l.applyTime, l.status, l.auditTime, l.opinion, c.classID " +
                "FROM sys_leave l " +
                "LEFT JOIN sys_course c ON l.courseID = c.courseID " +
                "WHERE c.classID = ? AND c.term = ?";
        List<Leave> leaveList = jdbcTemplate.query(sql, new Object[]{classID,term}, new RowMapper<Leave>() {
            @Override
            public Leave mapRow(ResultSet rs, int rowNum) throws SQLException {
                Leave leave = new Leave();
                leave.setLeaveID(rs.getString("leaveID"));
                leave.setCourseID(rs.getString("courseID"));
                leave.setReason(rs.getString("reason"));
                leave.setDayNum(rs.getString("dayNum"));
                leave.setStuNo(rs.getString("stuNo"));
                leave.setApplyTime(rs.getTimestamp("applyTime"));
                leave.setStatus(rs.getString("status"));
                leave.setAuditTime(rs.getTimestamp("auditTime"));
                leave.setOpinion(rs.getString("opinion"));
                // 这里不需要设置classID，因为它是通过课程表连接得到的
                return leave;
            }
        });

        return leaveList;
    }

    public void insert(Leave entity) {
        //声明插入SQL字符串变量sql
        String sql = "insert into sys_leave(leaveID,courseID,reason,daynum,stuNo,applytime)values(?,?,?,?,?,?)";
        if (entity != null) {
            String[] paramValues = {entity.getLeaveID(),entity.getCourseID(), entity.getReason(), entity.getDayNum(), entity.getStuNo(), String.valueOf(entity.getApplyTime())};
            jdbcTemplate.update(sql, paramValues);
        }
    }
    public void delete(String id){
        String sql ="delete from sys_leave where leaveID=?";
        String[]paramValues={id};
        jdbcTemplate.update(sql,paramValues);
    }

    public void update(Leave leave) {
        // 声明更新 SQL 字符串变量 sql
        String sql = "update sys_leave set status=?, audittime=?, opinion=? where leaveID=?";
        if (leave != null) {
            String[] paramValues = {leave.getStatus(), String.valueOf(leave.getAuditTime()),leave.getOpinion(),leave.getLeaveID()};
            System.out.println("Parameters: " + Arrays.toString(paramValues));
            jdbcTemplate.update(sql, paramValues);
        }
    }



    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
}
